

USE fat1_c1203i_s3_g2
go
if object_id('customer_temp') is not null
BEGIN
  drop table customer_temp
END
SELECT * INTO customer_temp FROM customer WHERE status='subcrible'
WHILE(SELECT COUNT(*) FROM customer_temp) > 0  
BEGIN
	DECLARE
		@customerID int,
		@emailSender varchar(200),
		@message varchar(max),
		@subject_sent varchar(200),
		@Receiver varchar(8000),
		@nameSender varchar(25),
		@title varchar(100),
		@message_ecard varchar(1000),
		@signatures varchar(25),
		@ecardId int,
		@sentEcardId int
		-- lay random ra id cua card can gui
		SELECT TOP 1 @ecardId = ecardId FROM Ecard where typeEcardId = 9 ORDER BY NEWID()
		-- set value for available
		select top 1 @customerID = customerId, @nameSender = name, @emailSender=email from customer_temp
		set @title ='have a nice day';
		set @message_ecard ='have a nice day';
		set @signatures = '';
		--insert vao bang sentecard
		insert into sentEcard values(@customerID,@ecardId,GETDATE(),@title,@message_ecard,@signatures)
		-- lay id cua cua thiep vua tao trong logsentmail de gui 
		select top 1 @sentEcardId =sentEcardId from sentEcard where customerId = @customerID order by sentEcardId desc 
	    -- tao 1 bang contactEmail_temp
		if object_id('contactEmail_temp') is not null
		BEGIN
		  drop table contactEmail_temp
		END
		SELECT * INTO contactEmail_temp FROM contactEmail WHERE customerId  = @customerID
		WHILE(SELECT COUNT(*) FROM contactEmail_temp) > 0  
		BEGIN
			DECLARE 
			@nameReceiver varchar(25),
			@emailReceiver varchar(200),
			@birthday datetime,
			@logSentMailId int
			-- set value for available
			select top 1 @nameReceiver = name ,@emailReceiver = email, @birthday =birthday from contactEmail_temp
			-- insert vao bang listmail
			insert into listEmail values(@sentEcardId,@emailReceiver,@nameReceiver)
			-- insert vao bang logsentmail
			insert into logSentMail values(@customerID,@nameSender,@emailSender,@ecardId,GETDATE(),@title,@message_ecard,@signatures,@emailReceiver,@nameReceiver)
			
			-- lay id cua cua thiep vua tao trong logsentmail de gui 
			select top 1 @logSentMailId =LogSentMailId from logSentMail where customerId = @customerID order by LogSentMailId desc 
			
		    SET @message ='['+@emailSender+'] just sent you an ecard from Ecard.com <br/>You can view it by clicking here:<br/> <a href="'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar) +'">'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar)+' </a>';
			--SET @message ='['+@emailSender+'] just sent you an ecard from Ecard.com <br/>
			--You can view it by clicking here:<br/> <a href="http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id="'+cast(@logSentMailId as varchar) +'>'+'http://localhost/Eproject_sem3_thanh/viewcardsent.aspx?id='+cast(@logSentMailId as varchar)+' </a>'
			Set @subject_sent = @emailSender +' sent you an ecard'

			EXEC msdb.dbo.sp_send_dbmail @profile_name='test profile',
			@recipients=@emailReceiver,
			@subject=@subject_sent,
			@body=@message ,
			@body_format = 'HTML' 
			DELETE TOP (1) PERCENT FROM contactEmail_temp 
		END
	
	update Ecard set counts = counts + 1 where ecardId = @ecardId
	DELETE TOP (1) PERCENT FROM customer_temp 
end


